const express=require('express')
const router=express.Router()
const db=require('../utrls/db')
router.post('/tologin',(req,res)=>{
    const {s_user,s_pass}=req.body;
    const sql=`SELECT * FROM t_student WHERE s_user='${s_user}' AND s_pass='${s_pass}';`;
    //执行sql 登录
    db.query(sql,(err,data)=>{
        //err  如果执行错误sql报错
        //data  执行成功返回分结果
        if(err){
            res.send({error:1,mesg:err})
        }else{
            if(data.length>0){
                res.send({error:0,msg:'登录成功'})
            }else{
                res.send({error:1,msg:'该用户未注册'})
            }
    
        }
    })
})
router.post('/toData',(req,res)=>{
    const {page,count}=req.body;
    const sql=`SELECT * FROM t_notice LIMIT ${(page-1)*count},${count};`;
    const sql1=`SELECT COUNT(*) AS total FROM t_notice;`

    //执行sql 登录
    db.query(sql,(err,data)=>{
        if(err){
            res.send({error:1,mesg:err})
        }else{
            db.query(sql1,(err2,data2)=>{
                if(err2){
                    res.send({error:1,mesg:err2})
                }else{
                    res.send({err:0,data:data,total:data2[0]})
                }
            })
        }
    })
})

router.post('/todorm',(req,res)=>{
    const{search,page,count}=req.body
    const sql=`SELECT d_id,d_name,d_cost,d_status,COUNT(s_d_id) AS people FROM
    (SELECT* FROM t_dorm LEFT JOIN t_student ON s_d_id=d_id WHERE d_name LIKE '%${search}%') AS ss GROUP BY d_id LIMIT ${(page-1)*count},${count};`;
    const sql1=`SELECT count(*) as total FROM t_dorm WHERE d_name LIKE '%${search}%';`
    db.query(sql,(err,data)=>{
        if(err){
            res.send({error:1,mesg:err})
        }else{
            db.query(sql1,(err1,data1)=>{
                if(err){
                    res.send({err:err})
                }else{
                    res.send({data:data,total:data1[0]})
                }
            })
        }
    })
})
//添加宿舍
router.post('/toAddDorm',(req,res)=>{
    const{dormName,dormMoney}=req.body
    const sql=`INSERT INTO t_dorm VALUE(NULL,'${dormName}',${dormMoney},DEFAULT);`
    db.query(sql,(err,data)=>{
        if(!err){
            res.send('添加成功')
        }
    })
})

//删除宿舍
router.post('/dormDel',(req,res)=>{
    const {ID}=req.body
    const sql=`DELETE FROM t_dorm WHERE d_id=${ID};`
    db.query(sql,(err,data)=>{
        if(err){
            res.send('该宿舍还有同学居住，无法删除!')
        }else{
            res.send('删除成功')
        }
    })
})

//修改宿舍
router.post('/editDorm',(req,res)=>{
    const{dormID,dormName,dormMoney}=req.body
    const sql=`UPDATE t_dorm SET d_name='${dormName}',d_cost=${dormMoney} WHERE d_id=${dormID};`
    db.query(sql,(err,data)=>{
        if(err){
            res.send({error:err})
        }else{
            res.send('修改成功')

        }
    })
})

//渲染学生管理界面
router.post('/tostudent',(req,res)=>{
    const{search,page,count}=req.body
    const sql=`SELECT * FROM t_student JOIN t_dorm WHERE s_name LIKE '%${search}%' AND t_student.s_d_id=t_dorm.d_id LIMIT ${(page-1)*count},${count};`
    const sql1=`SELECT count(*) as total FROM t_student WHERE s_name LIKE '%${search}%';`
    db.query(sql,(err,data)=>{
        if(!err){
            db.query(sql1,(err,data2)=>{
                res.send({data:data,total:data2[0]})
            })
        }
    })
})

//添加学生
router.post('/toAddStu',(req,res)=>{
    const{stuName,stuuser,stuPsd,stuDorm}=req.body
    const sql=`INSERT INTO t_student VALUE(NULL,'${stuName}',${stuuser},${stuPsd},${stuDorm});`
    db.query(sql,(err,data)=>{
        if(!err){
            res.send('添加成功')
        }
    })
})

//删除学生
router.post('/stuDel',(req,res)=>{
    const {ID}=req.body
    const sql=`DELETE FROM t_pay WHERE p_s_id=${ID};`
    const sql1=`DELETE FROM t_student WHERE s_id=${ID};`
    db.query(sql,(err,data)=>{
        if(!err){
            db.query(sql1,(err1,data1)=>{
                if(!err1){
                    res.send('删除成功')
                }else{
                    res.send({err:err1})
                }
            })
        }else{
            res.send({err:err})
        }
    })
})


//修改学生
router.post('/editStu',(req,res)=>{
    const{stuID1,stuName1,stuuser1,stuDorm1,stuPsd1}=req.body
    const sql=`UPDATE t_student SET s_name='${stuName1}',s_user=${stuuser1},s_d_id=${stuDorm1},s_pass=${stuPsd1} WHERE s_id=${stuID1};`
    db.query(sql,(err,data)=>{
        if(err){
            res.send({error:err})
        }else{
            res.send('修改成功')

        }
    })
})

//查看缴费记录
router.post('/stuPay',(req,res)=>{
    const {s_id}=req.body
    const sql=`SELECT * FROM t_pay WHERE p_s_id=${s_id};`
    db.query(sql,(err,data)=>{
        if(err){
            res.send({error:err})
        }else{
            res.send({data:data})
        }
    })
})

//渲染公告管理界面
router.post('/toNotice',(req,res)=>{
    const{search,page,count}=req.body
    const sql=`SELECT * FROM t_notice WHERE n_title LIKE '%${search}%' LIMIT ${(page-1)*count},${count};`
    const sql1=`SELECT count(*) as total FROM t_notice WHERE n_title LIKE '%${search}%';`
    db.query(sql,(err,data)=>{
        if(!err){
            db.query(sql1,(err,data2)=>{
                res.send({data:data,total:data2[0]})
            })
        }
    })
})
//新建公告
router.post('/getID',(req,res)=>{
    const{user}=req.body
    const sql=`SELECT a_id FROM t_admin WHERE a_user=${user};`
    db.query(sql,(err,data)=>{
        if(!err){
            res.send({data:data})
        }
    })
})
router.post('/toAddNot',(req,res)=>{
    const{notTitle,notContent,notTinme,notName}=req.body
    const sql=`INSERT INTO t_notice VALUE(NULL,'${notTitle}','${notContent}','${notTinme}',${notName});`
    db.query(sql,(err,data)=>{
        if(err){
            res.send({err:err})
        }else{
            res.send('添加成功')
        }
    })
})
//删除公告
router.post('/delNot',(req,res)=>{
    const {n_id}=req.body
    const sql=`DELETE FROM t_notice WHERE n_id=${n_id};`
    db.query(sql,(err,data)=>{
        if(err){
            res.send({err:err})
        }else{
            res.send('删除成功')
        }
    })
})
//修改公告
router.post('/editNot',(req,res)=>{
    const{notTitle1,notContent1,notID1}=req.body
    const sql=`UPDATE t_notice SET n_title='${notTitle1}',n_content='${notContent1}' WHERE n_id=${notID1};`
    db.query(sql,(err,data)=>{
        if(err){
            res.send({error:err})
        }else{
            res.send('修改成功')

        }
    })
})

//渲染管理员
router.post('/toAdmin',(req,res)=>{
    const{search,page,count}=req.body
    const sql=`SELECT * FROM t_admin WHERE a_name LIKE '%${search}%' LIMIT ${(page-1)*count},${count};`
    const sql1=`SELECT count(*) as total FROM t_admin WHERE a_name LIKE '%${search}%';`
    db.query(sql,(err,data)=>{
        if(!err){
            db.query(sql1,(err,data2)=>{
                res.send({data:data,total:data2[0]})
            })
        }
    })
})

//添加管理员
router.post('/toAddAdmin',(req,res)=>{
    const{adminName,adminUser,adminPass,editSymbol}=req.body
    const sql=`INSERT INTO t_admin VALUE(NULL,'${adminName}',${adminUser},${adminPass},${editSymbol=='普通管理员'?1:0});`
    db.query(sql,(err,data)=>{
        if(!err){
            res.send('添加成功')
        }
    })
})
//删除管理员
router.post('/AdminDel',(req,res)=>{
    const {ID}=req.body
    const sql=`DELETE FROM t_admin WHERE a_id=${ID};`
    db.query(sql,(err,data)=>{
        if(err){
            res.send({err:err})
        }else{
            res.send('删除成功')
        }
    })
})
//修改管理员
router.post('/editAdmin',(req,res)=>{
    const{adminId1,adminName1,adminUser1,adminPass1,adminType1}=req.body
    const sql=`UPDATE t_admin SET a_name='${adminName1}',a_user='${adminUser1}',a_pass=${adminPass1},a_status=${adminType1=='普通管理员'?1:0} WHERE a_id=${adminId1};`
    db.query(sql,(err,data)=>{
        if(err){
            res.send({error:err})
        }else{
            res.send('修改成功')

        }
    })
})
module.exports=router;